﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FreedomERP.Comm;
using FreedomERP.Models;
using FreedomERP.Models.Bd;
using FreedomERP.Dao.Bd;
using FreedomERP.Dao.Bd.Impl;
using FreedomERP.Dao.Inv;
using FreedomERP.Dao.Inv.Impl;
using System.IO;
using NPOI.HSSF.UserModel;
using FreedomERP.Comm.Utils;
using NPOI.SS.UserModel;
using System.Web;

namespace FreedomERP.Services.Bd.Impl
{
    /// <summary>
    /// 商品，库存管理业务层实现类
    /// </summary>
    public class BdProductServiceImpl : IBdProductService
    {
        IBdProductDao productDao = new BdProductDaoImpl();
        IBdOrgunitDao orgunitDao = new BdOrgunitDaoImpl();

        private String getValiableStatusID()
        {
            String result = null;
            DAOResult<String> orgunit = orgunitDao.findOrgunitId(ConstData.ORGUNIT_NAME_LC);
            String fOrgunitId = null;
            if (!orgunit.result)
            {
                throw new Exception(orgunit.msg);
            }
            else
            {
                fOrgunitId = orgunit.resultSet.First();
            }
            IBdCategoryDao bdCateDao = new BdCategoryDaoImpl();
            int totalCount = 0;
            DAOResult<BdCategory> status = bdCateDao.findProdStatusTypes(null, fOrgunitId, ConstData.PRODUCT_STATUS_YES, null, null, false, 0, 1, out totalCount);
            if (!status.result)
            {
                throw new Exception(status.msg);
            }
            else
            {
                if (status.resultSet.Count <= 0)
                {
                    throw new Exception("产品状态标示在数据库中不存在");
                }
                BdCategory statusCate = status.resultSet.First();
                result = statusCate.FId;
            }
            return result;
        }

        /// <summary>
        /// 获取商品信息，如果参数为空，则该参数不参与查询
        /// </summary>
        /// <param name="fNumber"></param>
        /// <param name="fName"></param>
        /// <param name="fStatusId"></param>
        /// <param name="fFromQty"></param>
        /// <param name="fEndQty"></param>
        /// <returns></returns>
        public List<BdProduct> getProducts(String fId, String fNumber, String fName, String fModelId, String fUnitId, String fReMark, String fFromQty, String fEndQty, String fProdInvStatusName, String sortColumn, Boolean isASC, int start, int limit, out int totalCount)
        {
            DAOResult<String> orgunit = orgunitDao.findOrgunitId(ConstData.ORGUNIT_NAME_LC);
            String fOrgunitId = null;
            if (!orgunit.result)
            {
                throw new Exception(orgunit.msg);
            }
            else
            {
                fOrgunitId = orgunit.resultSet.First();
            }
            DAOResult<BdProduct> result = productDao.getProducts(fId, fOrgunitId, fNumber, fName, fModelId, fUnitId, fReMark, getValiableStatusID(), fFromQty, fEndQty, fProdInvStatusName, sortColumn, isASC, start, limit, out totalCount);
            if (result.result == true)
            {
                return result.resultSet;
            }
            else
            {
                throw new Exception(result.msg);
            }

        }

        /// <summary>
        /// 获取商品信息，如果参数为空，则该参数不参与查询
        /// </summary>
        /// <param name="fNumber"></param>
        /// <param name="fName"></param>
        /// <param name="fStatusId"></param>
        /// <param name="fFromQty"></param>
        /// <param name="fEndQty"></param>
        /// <returns></returns>
       public List<LCProductInfo> getProductInfos(String fId, String fNumber, String fName, String fModelId, String fUnitId, String fReMark, String fProdInvStatusName, String sortColumn, Boolean isASC, int start, int limit, out int totalCount, string currAccountOrgunitId)
        {
            //DAOResult<String> orgunit = orgunitDao.findOrgunitId(ConstData.ORGUNIT_NAME_LC);
            //String fOrgunitId = null;
            //if (!orgunit.result)
            //{
            //    throw new Exception(orgunit.msg);
            //}
            //else
            //{
            //    fOrgunitId = orgunit.resultSet.First();
            //}
            DAOResult<ProductInfo> result = productDao.getProductInfos(fId, currAccountOrgunitId, fNumber, fName, fModelId, fUnitId, fReMark, getValiableStatusID(), null, null, fProdInvStatusName, sortColumn, isASC, start, limit, out totalCount);
            if (result.result == true)
            {
                List<LCProductInfo> lcProductInfos = new List<LCProductInfo>();
                foreach (var productInfo in result.resultSet)
                {
                    LCProductInfo lcProdInfo = new LCProductInfo(productInfo);
                    lcProductInfos.Add(lcProdInfo);
                }
                return lcProductInfos;
            }
            else
            {
                throw new Exception(result.msg);
            }
        }

        /// <summary>
        /// 保存产品信息
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public void saveOrUpdateProductInfo(LCProductInfo productinfo, String fAccountId, out String Fid)
        {
            ProductInfo prodInfo = productinfo;
            IBdCategoryDao cateDao = new BdCategoryDaoImpl();
            int totalCount = 0;
            var query = cateDao.findProdStatusTypes(null, null, ConstData.PRODUCT_STATUS_YES, null, null, false, 0, 1, out totalCount);
            if (query != null && query.resultSet != null && query.resultSet.Count>0)
            {
                String validStatusId = query.resultSet.First<BdCategory>().FId;
                prodInfo.ProductStatusId = validStatusId;
            }
            Hashtable prices = new Hashtable();
            if (productinfo.ProductPrice1 != null)
            {
                prices.Add(ConstData.CATEGORY_PROD_PRICE_LC_TYPE1, productinfo.ProductPrice1);
            }
            if (productinfo.ProductPrice2 != null)
            {
                prices.Add(ConstData.CATEGORY_PROD_PRICE_LC_TYPE2, productinfo.ProductPrice2);
            }
            if (productinfo.ProductPrice3 != null)
            {
                prices.Add(ConstData.CATEGORY_PROD_PRICE_LC_TYPE3, productinfo.ProductPrice3);
            }
            prodInfo.prices = prices;

            DAOResult<String> result = productDao.saveOrUpdateProductInfo(prodInfo, fAccountId);
            if (result.result == false)
            {
                throw new Exception(result.msg);
            }
            else
            {
                Fid = result.resultSet.First();
            }
        }

        /// <summary>
        /// 根据产品Id将其产品信息状态改为无效
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public void deleteProduct(String productId)
        {
            DAOResult<BdProduct> result = productDao.deleteProduct(productId);
            if (result.result == false)
            {
                throw new Exception(result.msg);
            }
        }

        public List<ProductModelType> getModelTypeList(string currAccountOrgunitFId)
        {
            //DAOResult<String> orgunit = orgunitDao.findOrgunitId(ConstData.ORGUNIT_NAME_LC);
            //String fOrgunitId = null;
            //if (!orgunit.result)
            //{
            //    throw new Exception(orgunit.msg);
            //}
            //else
            //{
            //    fOrgunitId = orgunit.resultSet.First();
            //}
            int totalCount = 0;
            IBdCategoryDao cateDao = new BdCategoryDaoImpl();
            DAOResult<BdCategory> modleTypes = cateDao.findProdModelTypes(null, currAccountOrgunitFId, null, null, null, ConstData.CATEGORY_STATUS_YES, false, 0, int.MaxValue, out totalCount);
            List<ProductModelType> result = new List<ProductModelType>();
            if (modleTypes.result != false)
            {

                foreach (var bdCate in modleTypes.resultSet)
                {
                    ProductModelType prodModle = new ProductModelType();
                    prodModle.FId = bdCate.FId;
                    prodModle.FName = bdCate.FName;
                    result.Add(prodModle);

                }
            }
            return result;

        }

        /// <summary>
        /// 根据产品FNumber获取产品信息
        /// </summary>
        /// <param name="productId"></param>
        /// <author>梁建东</author>
        /// <returns></returns>
        public ProductInfo GetProductMsgByFNumber(String FNumber)
        {
            return productDao.GetProductMsgByFNumber(FNumber);
        }

        /// <summary>
        /// 根据FId获取产品
        /// </summary>
        /// <param name="FId"></param>
        /// <author>姚少鹏</author>
        /// <returns></returns>
        public BdProduct getProductByFId(String FId)
        {
            return productDao.getProductByFId(FId);
        }

        /// <summary>
        /// 根据产品类型获取产品信息
        /// </summary>
        /// <param name="FType"></param>
        /// <author>姚少鹏</author>
        /// <returns></returns>
        public List<ProductInfo> getProductInfoByFType(String FType)
        {
            return productDao.getProductInfoByFType(FType);
        }

        /// <summary>
        /// 获取商家的全部商品单位
        /// <author>梁建东</author>
        /// </summary>
        public List<ProductUtil> getUnitList(string currAccountOrgunitFId)
        {
            int totalCount = 0;
            IBdCategoryDao cateDao = new BdCategoryDaoImpl();
            DAOResult<BdCategory> modleTypes = cateDao.findProdUnitTypes(null, currAccountOrgunitFId, null, null, null, ConstData.CATEGORY_STATUS_YES, false, 0, int.MaxValue, out totalCount);
            List<ProductUtil> result = new List<ProductUtil>();
            if (modleTypes.result != false)
            {

                foreach (var bdCate in modleTypes.resultSet)
                {
                    ProductUtil prodUtil = new ProductUtil();
                    prodUtil.FId = bdCate.FId;
                    prodUtil.FName = bdCate.FName;
                    result.Add(prodUtil);

                }
            }
            return result;

        }

        /// <summary>
        /// 统计产品进出货
        /// <author>梁建东</author>
        /// </summary>
        public List<ProductOutInCount> ProductOutInCount(Hashtable filters)
        {
            List<ProductOutInCount> list = new List<ProductOutInCount>();

            IBdCategoryDao cateDao = new BdCategoryDaoImpl();
            string receiveOrderStatusId = cateDao.QueryCategoryFIdByFTypeAndFNumber(ConstData.CATEGORY_RECEIVE_ORDER_STATUS, ConstData.CATEGORY_RECEIVE_ORDER_STATUS_APPROVE);
            filters.Add("FStatusId", receiveOrderStatusId);

            IInvReceiveOrderDetailDAO receiveOrderDetailDAO = new InvReceiveOrderDetailDAOImpl();
            List<InvReceiveOrderDetail> receiveDetailList = receiveOrderDetailDAO.GetReceiveOrderDetailListByFilter(filters);

            string sendOrderStatusId = cateDao.QueryCategoryFIdByFTypeAndFNumber(ConstData.CATEGORY_SEND_ORDER_STATUS, ConstData.CATEGORY_SEND_ORDER_STATUS_APPROVE);
            filters["FStatusId"] = sendOrderStatusId;

            IInvSendOrderDetailDao sendOrderDetailDAO = new InvSendOrderDetailDaoImpl();
            List<InvSendOrderDetail> sendDetailList = sendOrderDetailDAO.GetSendOrderDetailListByFilter(filters);

            Hashtable resultHT = new Hashtable();
            if (receiveDetailList != null) 
            {
                foreach (InvReceiveOrderDetail receiveDetail in receiveDetailList) 
                {
                    if (resultHT.ContainsKey(receiveDetail.FProductId))
                    {
                        ProductOutInCount outIntCount = (ProductOutInCount)resultHT[receiveDetail.FProductId];
                        outIntCount.FInQty += receiveDetail.FNumber;
                    }
                    else 
                    {
                        ProductOutInCount outIntCount = new ProductOutInCount();
                        outIntCount.FProductNumber = receiveDetail.BdProduct == null ? "" : receiveDetail.BdProduct.FNumber;
                        outIntCount.FProductName = receiveDetail.BdProduct == null ? "" : receiveDetail.BdProduct.FName;
                        outIntCount.FProductUnit = receiveDetail.BdProduct == null ? "" : (receiveDetail.BdProduct.BdCategory2 == null ? "" : receiveDetail.BdProduct.BdCategory2.FName);
                        outIntCount.FInQty = receiveDetail.FNumber;
                        outIntCount.FOutQty = 0L;
                        resultHT.Add(receiveDetail.FProductId, outIntCount);
                    }    
                }
            }
            if (sendDetailList != null)
            {
                foreach (InvSendOrderDetail sendDetail in sendDetailList)
                {
                    if (resultHT.ContainsKey(sendDetail.FProductId))
                    {
                        ProductOutInCount outIntCount = (ProductOutInCount)resultHT[sendDetail.FProductId];
                        outIntCount.FOutQty += sendDetail.FQty;
                    }
                    else
                    {
                        ProductOutInCount outIntCount = new ProductOutInCount();
                        outIntCount.FProductNumber = sendDetail.BdProduct == null ? "" : sendDetail.BdProduct.FNumber;
                        outIntCount.FProductName = sendDetail.BdProduct == null ? "" : sendDetail.BdProduct.FName;
                        outIntCount.FProductUnit = sendDetail.BdProduct == null ? "" : (sendDetail.BdProduct.BdCategory2 == null ? "" : sendDetail.BdProduct.BdCategory2.FName);
                        outIntCount.FInQty = 0L;
                        outIntCount.FOutQty = sendDetail.FQty;
                        resultHT.Add(sendDetail.FProductId, outIntCount);
                    }
                }
            }

            foreach (string key in resultHT.Keys)
            {
                list.Add((ProductOutInCount)resultHT[key]);
            }

            return list;
        }

        /// <summary>
        /// 导出统计产品进出货信息到excel
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void ExportProductOutInCountToExcel(Hashtable filters, string serverPath, HttpResponseBase response)
        {
            List<ProductOutInCount> countList = ProductOutInCount(filters);

            string templateFilePath = @"" + serverPath + "Upload\\Template\\ProductOutInCountExportTemplate.xls";
            FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);

            MemoryStream ms = new MemoryStream();

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

            HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderTop = BorderStyle.THIN;

            if (countList != null)
            {
                int r = 1;
                long outSum = 0L;
                long inSum = 0L;

                foreach (ProductOutInCount outInCount in countList)
                {
                    HSSFRow row = (HSSFRow)sheet.CreateRow(r);

                    HSSFCell codeCell = (HSSFCell)row.CreateCell(0);
                    codeCell.SetCellValue(outInCount.FProductNumber);
                    codeCell.CellStyle = style;

                    HSSFCell nameCell = (HSSFCell)row.CreateCell(1);
                    nameCell.SetCellValue(outInCount.FProductName);
                    nameCell.CellStyle = style;

                    HSSFCell unitCell = (HSSFCell)row.CreateCell(2);
                    unitCell.SetCellValue(outInCount.FProductUnit);
                    unitCell.CellStyle = style;

                    HSSFCell outCell = (HSSFCell)row.CreateCell(3);
                    outCell.SetCellValue(outInCount.FOutQty);
                    outCell.CellStyle = style;

                    HSSFCell inCell = (HSSFCell)row.CreateCell(4);
                    inCell.SetCellValue(outInCount.FInQty);
                    inCell.CellStyle = style;

                    r++;
                    outSum += outInCount.FOutQty;
                    inSum += outInCount.FInQty;
                }

                HSSFRow rowCount = (HSSFRow)sheet.CreateRow(r);

                HSSFCell codeCountCell = (HSSFCell)rowCount.CreateCell(0);
                codeCountCell.SetCellValue("合计");
                codeCountCell.CellStyle = style;

                HSSFCell nameCountCell = (HSSFCell)rowCount.CreateCell(1);
                nameCountCell.CellStyle = style;

                HSSFCell unitCountCell = (HSSFCell)rowCount.CreateCell(2);
                unitCountCell.CellStyle = style;

                HSSFCell outCountCell = (HSSFCell)rowCount.CreateCell(3);
                outCountCell.SetCellValue(outSum);
                outCountCell.CellStyle = style;

                HSSFCell inCountCell = (HSSFCell)rowCount.CreateCell(4);
                inCountCell.SetCellValue(inSum);
                inCountCell.CellStyle = style;
            }

            workbook.Write(ms);
            response.AddHeader("Content-Disposition", string.Format("attachment; filename=ProductOutInCount.xls"));
            response.BinaryWrite(ms.ToArray());
            workbook = null;
            ms.Close();
            ms.Dispose();
        }

        /// <summary>
        /// 获取产品库存状态类型
        /// </summary>
        /// <author>陈新杰</author>
        public List<ProductInvStatusType> getProdInvStatusTypeList()
        {
            DAOResult<String> orgunit = orgunitDao.findOrgunitId(ConstData.ORGUNIT_NAME_LC);
            String fOrgunitId = null;
            if (!orgunit.result)
            {
                throw new Exception(orgunit.msg);
            }
            else
            {
                fOrgunitId = orgunit.resultSet.First();
            }
            int totalCount = 0;
            IBdCategoryDao cateDao = new BdCategoryDaoImpl();
            DAOResult<BdCategory> modleTypes = cateDao.findBdCategories(null, fOrgunitId, ConstData.CATEGORY_PROD_INV_STATUS_TYPE, null, null, null, ConstData.CATEGORY_STATUS_YES, false, 0, int.MaxValue, out totalCount);
            List<ProductInvStatusType> result = new List<ProductInvStatusType>();
            if (modleTypes.result != false)
            {

                foreach (var bdCate in modleTypes.resultSet)
                {
                    ProductInvStatusType prodModle = new ProductInvStatusType();
                    prodModle.FId = bdCate.FId;
                    prodModle.FName = bdCate.FName;
                    prodModle.FNumber = bdCate.FNumber;
                    result.Add(prodModle);

                }
            }
            return result;
        }


        /// <summary>
        /// 导出产品信息到excel
        /// </summary>
        /// <author>陈新杰</author>
        /// <returns></returns>
        public void ExportProductsInfosToExcel(string serverPath, HttpResponseBase response, String fNumber, String fName, String fModelId, String fReMark, String fProdInvStatusName, String sort, String dir, string currAccountOrgunitId, string currAccountOrgunitName)
        {

            string templateFilePath = @"" + serverPath + "Upload\\Template\\ProductExportTemplate.xls";
            if (currAccountOrgunitName != null && currAccountOrgunitName.Equals(ConstData.ORGUNIT_NAME_GL))
            {
                templateFilePath = @"" + serverPath + "Upload\\Template\\ProductGeLiExportTemplate.xls";
            }
            FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);

            MemoryStream ms = new MemoryStream();

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

            HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderTop = BorderStyle.THIN;

            Boolean isASC = true;
            if ("DESC" == dir)
            {
                isASC = false;
            }
            int totoalcout = 0;
            IBdProductService productService = new BdProductServiceImpl();
            List<LCProductInfo> products = productService.getProductInfos(null, fNumber, fName, fModelId, null, fReMark, fProdInvStatusName, sort, isASC, 0, int.MaxValue, out totoalcout, currAccountOrgunitId);
            int firstDataColumn = 4;
            foreach (LCProductInfo prodInfo in products)
            {

                HSSFRow row = (HSSFRow)sheet.CreateRow(firstDataColumn);
                if (prodInfo != null)
                {
                    int k = 0;
                    HSSFCell codeCell = (HSSFCell)row.CreateCell(k);
                    codeCell.SetCellValue(prodInfo.ProductNumber);
                    codeCell.CellStyle = style;
                    k++;

                    HSSFCell nameCell = (HSSFCell)row.CreateCell(k);
                    nameCell.SetCellValue(prodInfo.ProductName);
                    nameCell.CellStyle = style;
                    k++;

                    HSSFCell modelCell = (HSSFCell)row.CreateCell(k);
                    modelCell.SetCellValue(prodInfo.ProductModelName);
                    modelCell.CellStyle = style;
                    k++;

                    HSSFCell price1Cell = (HSSFCell)row.CreateCell(k);
                    price1Cell.SetCellValue(prodInfo.ProductPrice1);
                    price1Cell.CellStyle = style;
                    k++;

                    HSSFCell price2Cell = (HSSFCell)row.CreateCell(k);
                    price2Cell.SetCellValue(prodInfo.ProductPrice2);
                    price2Cell.CellStyle = style;
                    k++;

                    HSSFCell price3Cell = (HSSFCell)row.CreateCell(k);
                    price3Cell.SetCellValue(prodInfo.ProductPrice3);
                    price3Cell.CellStyle = style;
                    k++;

                    HSSFCell qtyCell = (HSSFCell)row.CreateCell(k);
                    qtyCell.SetCellValue(prodInfo.ProductQty.ToString());
                    qtyCell.CellStyle = style;
                    k++;

                    HSSFCell remarkCell = (HSSFCell)row.CreateCell(k);
                    remarkCell.SetCellValue(prodInfo.ProductRemark);
                    remarkCell.CellStyle = style;
                    k++;
                }
                firstDataColumn++;
            }
            workbook.Write(ms);
            response.AddHeader("Content-Disposition", string.Format("attachment; filename=Product_" + DateUtils.GetCurDateFormat("yyyyMMddHHmmss") + ".xls"));
            response.BinaryWrite(ms.ToArray());
            workbook = null;
            ms.Close();
            ms.Dispose();
        }



    }



}
